package com.example.batch;/**
 * Created by kete on 2019-05-13.
 */

import com.google.common.io.Files;
import com.google.common.io.Resources;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.net.URL;
import java.nio.charset.Charset;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 作者：kete
 * 创建时间：2019-05-13 14:26
 * 功能描述：
 * 版本：
 */
public class PoiExcelTest {

    private Logger logger = LoggerFactory.getLogger(PoiExcelTest.class);


    private Map<String,String> billMap = new HashMap<>();

    public String rendFile(){

        String json = null;
        try {
            URL url = Resources.getResource("city.txt");
            File file = new File(url.getPath());
            if(file.exists()) {
                json = Files.toString(file, Charset.forName("UTF-8"));

            }
        } catch(IllegalArgumentException ex) {
            logger.info("starts.json not found");
        } catch(Exception ioex) {
            ioex.printStackTrace();
        }

        return json;


    }

    public void initBillName(){
        String text = rendFile();
        List<String> strList = Arrays.asList(text.split("\r\n"));
        strList.forEach(str->{
            String[] temp = str.split("\\|");
            //logger.info("bill_name {}----------- bill_county {}",temp[0],temp[1]);
            billMap.put(temp[0],temp[1]);
        });
    }

    public void parseExcel() throws Exception{
        initBillName();

        InputStream inputStream = new FileInputStream("D:\\temp\\lx\\test.xlsx");
        //InputStream inp = new FileInputStream("workbook.xlsx");

        Workbook workbook = WorkbookFactory.create(inputStream);
        Sheet sheet = workbook.getSheetAt(0);

        int rows = sheet.getLastRowNum() + 1;

        for (int row = 1; row < rows; row++){
            Row r = sheet.getRow(row);

            Cell cell = r.getCell(5);

            if (cell == null){
                logger.info("cell is null");
            }else{
                String key = cell.getStringCellValue();

                logger.info("key {}",key);

                String billCounty = billMap.get(key);

                if(billCounty != null && billCounty.length()>0){

                    Cell cell8 = r.getCell(8);

                    if(cell8 == null ){
                        cell8 = r.createCell(8);
                    }

                    cell8.setCellValue(billCounty);
                }
            }
        }

        FileOutputStream fileOut = new FileOutputStream("D:\\temp\\lx\\test.xlsx");
        workbook.write(fileOut);
        fileOut.close();
    }

    public static void main(String[] args) {
        PoiExcelTest test = new PoiExcelTest();

        try {
            test.parseExcel();
        } catch (Exception e) {
            e.printStackTrace();
        }


    }


}
